
import pymysql
import time, configparser
import threading




class SqlTest():

	def __init__(self):

		self.day = time.strftime("%Y%m%d")
		self.cf = configparser.ConfigParser()
		self.cf.read("account.ini")
		self.latestPos = self.cf.get("pos", "latestPos")
		# 存放最近一次


	# 连接数据库
	def conn_db(self):
		self.conn = pymysql.connect(host = "172.16.126.139",
													port = 3306,
													user = "root",
													password = "",
													charset = "gbk",
													db = "hqjy",
													cursorclass = pymysql.cursors.DictCursor)
		print("--- mysql connect succ ---")
	

	# 关闭数据库
	def close_db(self):
		self.conn.close()
		print("--- mysql connect close ---")


	# 检查记录中的账号，或者设备码是否在缓存中
	def is_account_has_count(self, row):

		flag = 0
		fund_account = ""
		imsi = ""
		i_fund_account = row["fund_account"]
		i_imsi = row["op_station"].split("_")[1]

		try:
			rt = self.cf.get("account", i_fund_account)
		except:
			flag = 1
			fund_account = i_fund_account
			self.cf.set("account", fund_account, "1")

		try:
			rt = self.cf.get("imsi", i_imsi)
		except:
			flag = 1
			imsi = i_imsi
			self.cf.set("imsi", imsi, "1")

		if(flag == 1):
			self.cf.write(open("account.ini", "w"))
		
		return flag, fund_account, imsi


	# 处理结果集
	def do_with_data(self, cr, start):

		hasnext = True
		position_str = ""
		new_account = []
		new_imsi = []

		is_new = 0
		n_fund_account = ""
		n_imsi = ""

		row = cr.fetchone()

		# 第一个记录为空个，表示没记录了
		if(row == None):
			hasnext = False

		if(start == 1 and row != None):
			position_str = row["position_str"]

		while(row != None):

			# 已找到最新的记录了，不用判断下面的记录了
			if(row["position_str"] == self.latestPos):
				hasnext = False
				break

			# 检查行记录是否在缓存的结果集中
			is_new, n_fund_account, n_imsi = self.is_account_has_count(row)
			if(is_new == 1):
				if(n_fund_account != ""):
					new_account.append(n_fund_account)

				if(n_imsi != ""):
					new_imsi.append(n_imsi)

			row = cr.fetchone()

		return hasnext, position_str, new_account, new_imsi


	# 定时执行任务
	def runTimer(self):

		self.testCase()
		delay = 300
		timer = threading.Timer(delay, self.runTimer)
		timer.start()


	def testSql(self):
		self.conn_db()

		with self.conn.cursor() as cursor:	
			sqlstr = "select * from trade_entrust_bonree t order by t.position_str desc limit %d, %d" % (1, 200)
			cursor.execute(sqlstr)
			rows = cursor.fetchall()
			for row in rows:
				imsi = row["op_station"].split("_")
				print(imsi)
				if(imsi[1] == "99000643257891"):
					print(row)



	def testCase(self):

		print("--- case start ---")
		self.conn_db()

		try:

			with self.conn.cursor() as cursor:

				start = 1
				end = 10
				loop = True
				firstPos = ""

				account_list_all = []
				imsi_list_all = []

				while(loop):

					# sqlstr = "select * from trade_entrust_bonree t order by t.position_str desc limit %d, %d where t.init_date = %s" % (start, end, self.day)
					sqlstr = "select * from trade_entrust_bonree t order by t.position_str desc limit %d, %d" % (start, end)
					cursor.execute(sqlstr)
					
					row = cursor.fetchone()

					hasnext, position_str, account_list, imsi_list = self.do_with_data(cursor, start)

					# 标记最新的记录
					if(position_str != ""):
						self.latestPos = position_str
						self.cf.set("pos", "latestPos", position_str)
						self.cf.write(open("account.ini", "w"))

					if(len(account_list) > 0):
						account_list_all = account_list_all + account_list

					if(len(imsi_list) > 0):
						imsi_list_all = imsi_list_all + imsi_list

					if(hasnext == True):
						start = end
						end = start + 10
					else:
						loop = False

				# 判断是否有新账号或者新设备，如果有发邮件
				# if(len(account_list_all) > 0 or len(imsi_list_all) > 0):
				# 	self.do_firefox(account_list_all, imsi_list_all)
				return account_list_all, imsi_list_all


		finally:
			self.close_db()
			print("--- case end ---")



if __name__ == "__main__":

	st = SqlTest()
	# print(st.testCase())
	st.testSql()